from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()



class Teacher(db.Model):
    id = db.Column(db.Integer,primary_key=True,autoincrement=True,comment="教师id")
    name = db.Column(db.String(16),default="名称",nullable=False,comment="用户名")
    attention_num = db.Column(db.Integer,default=0,comment="关注数量")
    phone = db.Column(db.String(11),unique=True,nullable=False,comment="手机号")#unique唯一
    password = db.Column(db.String(16),nullable=False,comment="密码")
    info = db.Column(db.String(256),default="",comment="简介")
    create_time = db.Column(db.DateTime,default=datetime.now,comment="创建时间")
    status = db.Column(db.Integer,default=0,comment="上线状态")
    photo = db.Column(db.String(256),default="http://xxxxx/def_photo",comment="头像")
    students = db.relationship("Stu",secondary="student2teacher",backref=db.backref("teachers"))




class Stu(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="教师id")
    name = db.Column(db.String(16), default="名称", nullable=False, comment="用户名")
    phone = db.Column(db.String(11), unique=True, nullable=False, comment="手机号")  # unique唯一
    password = db.Column(db.String(16), nullable=False, comment="密码")
    gender = db.Column(db.Integer, default=1, doc='1男0女')
    info = db.Column(db.String(256), default="", comment="简介")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")
    status = db.Column(db.Integer, default=0, comment="上线状态")
    photo = db.Column(db.String(256), default="http://xxxxx/def_photo", comment="头像")
    money = db.Column(db.Float(5,2),default=0.00,comment="学习币")
    course_num = db.Column(db.Integer,default=0,comment="特色课数量")
    solo_num = db.Column(db.Integer,default=0,comment="一对一课数量")
    vip = db.Column(db.DateTime,default=datetime.now,comment="会员")
    courorde_id = db.relationship("CourseOrder",backref="stu")
    course = db.relationship("Course", secondary="stu2course", backref=db.backref("stu"))
    appraise_id = db.relationship("Appraise", backref="stu")
    reservedcourse_id = db.relationship("ReservedCourse",backref="stu")
    tea = db.relationship("Teacher", secondary="student2teacher", backref=db.backref("tea"))


student2teacher = db.Table(
    "student2teacher",
    db.Column("id",db.Integer,primary_key=True,autoincrement=True),
    db.Column("student_id",db.Integer,db.ForeignKey("stu.id")),
    db.Column("teacher_id",db.Integer,db.ForeignKey("teacher.id")),
    db.UniqueConstraint("student_id","teacher_id",)
)


#课程大纲表
class Course(db.Model):

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(16), nullable=False, default="名称", comment="课程名字")
    info = db.Column(db.String(256), default="。。。", comment="简介")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")
    start_time = db.Column(db.DateTime, nullable=True, comment="开课时间")
    end_time = db.Column(db.DateTime, nullable=True, comment="结束时间")
    total = db.Column(db.DateTime,nullable=True,comment="本课程包含几节课")
    total_time = db.Column(db.Integer,nullable=True,comment="总时长(秒数)")
    photo = db.Column(db.String(256), default="http://xxxxxx/default_photo", comment="书皮")
    money = db.Column(db.Float(5, 2), default=0.00, comment="价格")
    type = db.Column(db.Integer, default=1, comment="{1:特色课，2:一对一}")
    teacher_num = db.Column(db.Integer, default=0, comment="讲师人数")
    collect_num = db.Column(db.Integer, default=0, comment="收藏量")
    course_num = db.Column(db.Integer, default=0, comment="预约量")
    buy_num = db.Column(db.Integer, default=0, comment="购买量")
    stu_num = db.Column(db.Integer,default=0,comment="购买数量")
    solo_num = db.Column(db.Integer, default=0, comment="一对一课数量")
    vip_free = db.Column(db.DateTime, default=0, comment="会员")
    excerpt = db.relationship("Excerpt",backref="course")
    courorder_id = db.relationship("CourseOrder",backref="course")
    reservedcourse_id = db.relationship("ReservedCourse", backref="course")
    tea = db.relationship("Teacher", secondary="course2teacher", backref=db.backref("course"))

#课程与用户的中间表    收藏表
stu2course = db.Table(
    "stu2course",
    db.Column("id",db.Integer,primary_key=True,autoincrement=True),
    db.Column("student_id",db.Integer,db.ForeignKey("stu.id")),
    db.Column("course_id",db.Integer,db.ForeignKey("course.id")),
)


#课节表
class Excerpt(db.Model):

    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name =db.Column(db.String(16),nullable=False,default="名称",comment="课节名称")
    course_id = db.Column(db.Integer,db.ForeignKey("course.id"),comment="课程id")
#
#
#课程和教师的中间表
course2teacher = db.Table(
    "course2teacher",
    db.Column("id",db.Integer,primary_key=True,autoincrement=True),
    db.Column("course_id",db.Integer,db.ForeignKey("course.id")),
    db.Column("teacher_id",db.Integer,db.ForeignKey("teacher.id")),
)



#课程订单表
class CourseOrder(db.Model):
    id = db.Column(db.Integer,primary_key=True,autoincrement=True,comment="订单id")
    c_id = db.Column(db.Integer,db.ForeignKey("course.id"),comment="关联课程的id")
    s_id = db.Column(db.Integer,db.ForeignKey("stu.id"),comment="学生的id")
    create_time = db.Column(db.DateTime,default=datetime.now(),comment="下单时间")
    fail = db.Column(db.Boolean,comment="退款")
    coupon_id = db.relationship("Coupon",backref="cou")
    # qusetion_id = db.relationship("Qusetion",backref="cou")


#优惠券
class Coupon(db.Model):
    id = db.Column(db.Integer,primary_key=True,autoincrement=True,comment="优惠券的id")
    name = db.Column(db.String(32),comment="优惠券名称")
    create_time = db.Column(db.DateTime,default="",comment="生效时间")
    end_time = db.Column(db.DateTime,default="",comment="失效日期")
    couord_id = db.Column(db.Integer, db.ForeignKey("course_order.id"),comment="课程订单id")

#评价表
class Appraise(db.Model):
    id = db.Column(db.Integer,primary_key=True,autoincrement=True,comment="评价表id")
    content = db.Column(db.TEXT,default="",comment="评价内容")
    create_time = db.Column(db.DateTime,default=datetime.now,comment="评论时间")
    course_id = db.Column(db.Integer,db.ForeignKey("course.id"),comment="课程id")
    stu_id = db.Column(db.Integer,db.ForeignKey("stu.id"),comment="用户id")

#预约表
class Reserved(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="预约表id")
    status = db.Column(db.Integer,default="0")


#约课订单表
class ReservedCourse(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="约课订单id")
    create_time = db.Column(db.DateTime,default=datetime.now,comment="订单创建时间")
    cou_time = db.Column(db.DateTime,default="",comment="课程开始时间")
    cou_end_time = db.Column(db.DateTime, default="", comment="课程结束时间")
    status = db.Column(db.Integer,default="0",comment="0:待上课,1:已上课,2:已取消上课")
    s_id = db.Column(db.Integer,db.ForeignKey("stu.id"),comment="用户id")
    cour_id = db.Column(db.Integer,db.ForeignKey("course.id"),comment="课程id")

#题库选择考点
class Qusetion(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="题库id")
    name = db.Column(db.String(32),comment="考点名称")
    my_id = db.Column(db.Integer,default="",nullable=True,comment="自关联id")
    course_id = db.Column(db.Integer,db.ForeignKey("course.id"),comment="课程id")


#套卷练习表
class TestPaper(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="套卷id")
    name1 = db.Column(db.String(32),comment="名称")
    name2 = db.Column(db.Integer,nullable=True,default="",comment="子关联id")
    type = db.Column(db.Integer,default="",comment="0:未作,1:已做")
    tiku = db.relationship("Thequestionbankpractice", backref="test_paper")
#套卷题库表
class Thequestionbankpractice(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="题库练习id")
    name = db.Column(db.String(32),nullable=True,comment="套卷题库名")
    to_name = db.Column(db.String(3), nullable=True, default='', comment='选项')
    test_paper_id = db.Column(db.Integer, db.ForeignKey("test_paper.id"), comment="试卷id")

#轮播图
class Carouse(db.Model):
    __tablename__ = "carouse"
    id = db.Column(db.Integer,primary_key=True,autoincrement=True,doc='id')
    url = db.Column(db.String(256),doc='图片地址')
    link_type = db.Column(db.Integer,default=0,doc="{0:无连接,1:连接,2:课程,3:资讯}")
    link_content = db.Column(db.String(256),doc="跳转内容")

